package com.jiudao.dao;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.google.gson.JsonObject;
import com.jiudao.entity.Branch;
import com.jiudao.entity.JylRiskconPerson;
import com.jiudao.entity.Riskcon;
import com.jiudao.entity.Risklist;

/**
 * 
 * @author Administrator 客群分析
 */
@Repository
public class CustomerGADao extends BaseDao {

	/**
	 * @param param
	 * @return String 客群分析数据查询
	 */
	public JsonObject getData(final JsonObject param) throws Exception {

		final StringBuilder basesql = new StringBuilder();
		final JsonObject retObj = new JsonObject();

		basesql.append("SELECT count(*) num,sum(").append(riskcon.column(Riskcon.TMOUNT));
		basesql.append(") tmount,").append(risklist.column(Risklist.CLASSNAME));
		basesql.append(" classname from ").append(riskcon.tablename());
		basesql.append(" AS ").append(riskcon.getTname());

		basesql.append(" LEFT JOIN ").append(jylRiskconPerson.tablename());
		basesql.append(" AS ").append(jylRiskconPerson.getTname());
		basesql.append(" on ").append(riskcon.column(Riskcon.POLICYNO));
		basesql.append("=").append(jylRiskconPerson.column(JylRiskconPerson.POLICYNO));

		basesql.append(" LEFT JOIN ").append(risklist.tablename());
		basesql.append(" AS ").append(risklist.getTname());
		basesql.append(" on ").append(risklist.column(Risklist.CLASSCODE));
		basesql.append("=").append(riskcon.column(Riskcon.CLASSCODE));

		basesql.append(" LEFT JOIN ").append(branch.tablename());
		basesql.append(" AS ").append(branch.getTname());
		basesql.append(" on ").append(branch.column(Branch.BRANCH));
		basesql.append("=").append(riskcon.column(Riskcon.BRANCH));
		basesql.append(" where 1=1 ");
		try {
			final String beginDate = param.get("beginDate").getAsString();
			final String endDate = param.get("endDate").getAsString();
			final int riskNum = param.get("riskNum").getAsInt();
			final String branch = param.get("branch").getAsString();

			if (0 < riskNum)
				basesql.append(" and ").append(jylRiskconPerson.column(JylRiskconPerson.RISK_NUM)).append("=")
						.append(riskNum);
			if (!StringUtils.isEmpty(beginDate))
				basesql.append(" and '").append(beginDate).append("'<= ").append(riskcon.column(Riskcon.APPDATE));
			if (!StringUtils.isEmpty(param.get("endDate").getAsString()))
				basesql.append(" and ").append(riskcon.column(Riskcon.APPDATE)).append(" <= '").append(endDate)
						.append("'");
			if (!StringUtils.isEmpty(branch))
				basesql.append(" and ").append(riskcon.column(Riskcon.BRANCH)).append(" = '").append(branch)
						.append("'");
		} catch (Exception e) {
			logger.error(e);
			return retObj;
		}

		final StringBuilder groupsql = new StringBuilder();
		groupsql.append(" group by ").append(risklist.column(Risklist.CLASSNAME));
		groupsql.append(" order by num desc limit 5");

		final StringBuilder ysql = new StringBuilder(basesql);
		ysql.append(" and ").append(jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK));
		ysql.append("  ='阳光青年'").append(groupsql);
		final StringBuilder qsql = new StringBuilder(basesql);
		qsql.append(" and ").append(jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK));
		qsql.append("  ='精英才俊'").append(groupsql);
		final StringBuilder xsql = new StringBuilder(basesql);
		xsql.append(" and ").append(jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK));
		xsql.append("  ='小康之家'").append(groupsql);
		final StringBuilder jsql = new StringBuilder(basesql);
		jsql.append(" and ").append(jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK));
		jsql.append("  ='家大业大'").append(groupsql);

		final String columns = "num,tmount,classname";
		try {
			retObj.add("ygqndata", executeQuery(ysql.toString(), columns));
			retObj.add("jycjdata", executeQuery(qsql.toString(), columns));
			retObj.add("xkzjdata", executeQuery(xsql.toString(), columns));
			retObj.add("jdyddata", executeQuery(jsql.toString(), columns));
		} catch (Exception e) {
			logger.error(e);
		}
		return retObj;
	}
}
